package io;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import data.Document;

public class DBDatasetReader extends DatasetReader
{

	private String m_DbUser;
	private String m_DbPwd;
	private String m_DbName;
	private String m_DbIp;
	private String m_Port;
	private String m_TrainingTable;
	private String m_SourceTable;
	private String m_DbUrl;
	private String m_TokenDelimiter;
	private String m_ValueDelimiter;
	
	public DBDatasetReader(String ip, String port)
	{
		String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		Properties prop = null;
		try
		{
			Class.forName(driverClass);

			final String propFile = "data\\conf.properties";
			PropertiesLoader propLoader = new PropertiesLoader(propFile);
			prop = propLoader.getPropertiesObject();
		
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch(FileNotFoundException e)
		{
			
		} catch(IOException e)
		{
			
		}
		
		if(null == prop)
		{
			prop = new Properties();
			prop.setProperty("dbName", "KNNTest");
			prop.setProperty("dbIP", "192.168.0.6");
			prop.setProperty("dbPort", "1433");
			prop.setProperty("trainingData", "training");
			prop.setProperty("sourceData", "testClassify");
			prop.setProperty("dbUser", "sa");
			prop.setProperty("dbPwd", "mobey");
			prop.setProperty("tokenDelimiter", "\t");
			prop.setProperty("valueDelimiter", ",");
		}
		
		m_DbName = prop.getProperty("dbName");
		m_DbIp = prop.getProperty("dbIP");
		if (m_DbIp != ip)
			m_DbIp = ip;
		m_Port = prop.getProperty("dbPort");
		if (m_Port != port)
			m_Port = port;
		m_TrainingTable = prop.getProperty("trainingData");
		m_SourceTable = prop.getProperty("sourceData");
		m_DbUser = prop.getProperty("dbUser");
		m_DbPwd = prop.getProperty("dbPwd");
		m_TokenDelimiter = prop.getProperty("tokenDelimiter");
		m_ValueDelimiter = prop.getProperty("valueDelimiter");

		m_DbUrl = "jdbc:sqlserver://" + m_DbIp + ":" + m_Port
				+ ";DatabaseName=" + m_DbName;
	}
	
	
	@Override
	@Deprecated
	public List<Document<String, Integer>> getDataset(boolean readClassId)
	{
		
		List<Document<String, Integer>> dataset = new ArrayList<Document<String, Integer>>();
		
		return dataset;
	}
	
	public List<Document<String, Integer>> getDataset(boolean readClassId, boolean trainingData, boolean all)
	{
		List<Document<String, Integer>> dataset = new ArrayList<Document<String, Integer>>();

		//	Connect to the data base
		Connection con = null;
		Statement stmt = null;
		String queryData;
		//	if read training data
		if(trainingData)
			queryData = "select * from " + m_TrainingTable;
		else
		{
			if(all)
				queryData = "select * from " + m_SourceTable;
			else
			{
				queryData = "select * from " + m_SourceTable + " where minorClass = -1";
			}
		}
		ResultSet rs = null;
		try
		{
			con = DriverManager.getConnection(m_DbUrl,m_DbUser,m_DbPwd);
			stmt = con.createStatement();
			rs = stmt.executeQuery(queryData);
		} catch (SQLException e)
		{
			e.printStackTrace();
		}

		//	Read data from database
		try
		{
			while(rs.next())
			{
				String tokens = rs.getString("tokens");
				String id = rs.getString("id");
				int classId = -1;
				int majorId = -1;
				if(readClassId)
				{
					classId = rs.getInt("minerClass");
					majorId = rs.getInt("majorClass");
					
				}
				
				String[] token = tokens.split(m_TokenDelimiter);
				Map<String, Integer> vec = new HashMap<String, Integer>();
				for(int i = 0; i < token.length; ++i)
				{
					String[] pair = token[i].split(m_ValueDelimiter);
					int count = Integer.parseInt(pair[1]);
					vec.put(pair[0], count);
				}
				Document<String, Integer> doc = new Document<String, Integer>(vec, classId, majorId, id);
				dataset.add(doc);
			}
			
			rs.close();
			stmt.close();
			con.close();
			
		} catch (SQLException e)
		{
			e.printStackTrace();
		}

		
		return dataset;
	}
	
	
	
	public String getM_DbName()
	{
		return m_DbName;
	}


	public void setM_DbName(String dbName)
	{
		m_DbName = dbName;
	}


	public String getM_TrainingTable()
	{
		return m_TrainingTable;
	}


	public void setM_TrainingTable(String trainingTable)
	{
		m_TrainingTable = trainingTable;
	}


	public String getM_SourceTable()
	{
		return m_SourceTable;
	}


	public void setM_SourceTable(String sourceTable)
	{
		m_SourceTable = sourceTable;
	}


	public static void main(String[] args)
	{
		System.out.println("Begin");
		DBDatasetReader reader = new DBDatasetReader("192.168.0.6", "1433");
		List<Document<String, Integer>> dataset = reader.getDataset(true);
	}

}
